﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DALayer;

namespace BALayer
{
    public class DBTTHoatDong
    {
        DAL db=null;
        public DBTTHoatDong()
        {
            db = new DAL();
        }
        public DataSet LayTTHoatDong()
        {
            return db.ExecuteQueryDataSet("select MaHD,ThoiGian,ThoiGianKetThuc,DiaDiem,SoLuong,KinhPhiHoTro,KinhPhiKhac,MaQL,MaDVTC from TTHoatDong", CommandType.Text, null);
        }
        public bool ThemTTHoatDong(ref string err, string MaHD, DateTime ThoiGian, DateTime ThoiGianKetThuc, string DiaDiem, int SoLuong,
            double KinhPhiHoTro, double KinhPhiKhac, string MaQL, string MaDVTC, bool dg)
        {
            return db.MyExecuteNonQuery("ThemTTHoatDong",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaHD", MaHD),
                new SqlParameter("@ThoiGian", ThoiGian),
                new SqlParameter("@ThoiGianKetThuc", ThoiGianKetThuc),
                new SqlParameter("@DiaDiem", DiaDiem),
                new SqlParameter("@SoLuong", SoLuong),
                new SqlParameter("@KinhPhiHoTro", KinhPhiHoTro),
                new SqlParameter("@KinhPhiKhac", KinhPhiKhac),
                new SqlParameter("@MaQL", MaQL),
                new SqlParameter("@MaDVTC", MaDVTC),
                new SqlParameter("@ChiTieuTienDo", dg),
                new SqlParameter("@ChiTieuChatLuong", dg),
                new SqlParameter("@ChiTieuHieuQuaKinhPhi", dg));
        }
        public bool SuaTTHoatDong(ref string err, string MaHD, DateTime ThoiGian, DateTime ThoiGianKetThuc, string DiaDiem, int SoLuong,
            double KinhPhiHoTro, double KinhPhiKhac, string MaQL, string MaDVTC)
        {
            return db.MyExecuteNonQuery("SuaTTHoatDong",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaHD", MaHD),
                new SqlParameter("@ThoiGian", ThoiGian),
                new SqlParameter("@ThoiGianKetThuc", ThoiGianKetThuc),
                new SqlParameter("@DiaDiem", DiaDiem),
                new SqlParameter("@SoLuong", SoLuong),
                new SqlParameter("@KinhPhiHoTro", KinhPhiHoTro),
                new SqlParameter("@KinhPhiKhac", KinhPhiKhac),
                new SqlParameter("@MaQL", MaQL),
                new SqlParameter("@MaDVTC", MaDVTC));
        }
        public bool XoaTTHoatDong(ref string err, string MaHD, DateTime ThoiGian)
        {
            return db.MyExecuteNonQuery("XoaTTHoatDong",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaHD", MaHD),
                new SqlParameter("@ThoiGian", ThoiGian));
        } 

        public DataSet LayDanhGiaHoatDong()
        {
            return db.ExecuteQueryDataSet("SELECT t.MaHD, t.ThoiGian, CASE " +
                " WHEN (k.SL>=t.SoLuong) THEN 'True'" +
                " ELSE 'False'" +
                " END 'ChiTieuSoLuong', t.ChiTieuTienDo, t.ChiTieuChatLuong, t.ChiTieuHieuQuaKinhPhi" +
                " FROM TTHoatDong t left join " +
                " (select d.MaHD, d.ThoiGian, COUNT(*) as SL from DanhGia d" +
                " group by d.MaHD, d.ThoiGian) as k on t.MaHD=k.MaHD and t.ThoiGian=k.ThoiGian", CommandType.Text, null);
        }

        //Sua danh gia hoat dong
        public bool SuaDanhGiaHoatDong(ref string err, string MaHD, DateTime ThoiGian, Boolean tiendo, Boolean chatluong, Boolean kinhphi)
        {
            return db.MyExecuteNonQuery("SuaDanhGiaHD",
                CommandType.StoredProcedure, ref err,
                new SqlParameter("@MaHD", MaHD),
                new SqlParameter("@ThoiGian", ThoiGian), 
                new SqlParameter("@ChiTieuTienDo",tiendo),
                new SqlParameter("@ChiTieuChatLuong",chatluong),
                new SqlParameter("@ChiTieuHieuQuaKinhPhi",kinhphi));
        }

        public DataSet TimKiemTTHoatDong(string text, string thang, string nam)
        {
            return db.ExecuteQueryDataSet("select MaHD,ThoiGian,ThoiGianKetThuc,DiaDiem,SoLuong,KinhPhiHoTro,KinhPhiKhac,MaQL,MaDVTC " +
                " from TTHoatDong t"+
                " where MaHD in (select MaHD "+
				                " from CTHoatDong"+
				                " where TenHD like N'%"+text+"%')"+
                "and MONTH(t.ThoiGian) like '%" + thang + "%' and YEAR(t.ThoiGian) like '%" + nam + "%'", CommandType.Text, null);
        }

        public DataSet TimKiemDGHoatDong(string text, string thang, string nam)
        {
            return db.ExecuteQueryDataSet("SELECT t.MaHD, t.ThoiGian, CASE " +
                " WHEN (k.SL>=t.SoLuong) THEN 'True' " +
                " ELSE 'False'" +
                " END 'ChiTieuSoLuong', t.ChiTieuTienDo, t.ChiTieuChatLuong, t.ChiTieuHieuQuaKinhPhi" +
                " FROM TTHoatDong t left join " +
                " (select d.MaHD, d.ThoiGian, COUNT(*) as SL from DanhGia d" +
                " group by d.MaHD, d.ThoiGian) as k on t.MaHD=k.MaHD and t.ThoiGian=k.ThoiGian " +
                " where t.MaHD in (select MaHD " +
                    " from CTHoatDong" +
                    " where TenHD like N'%" + text + "%')" +
                " and MONTH(t.ThoiGian) like '%" + thang + "%' and YEAR(t.ThoiGian) like '%" + nam + "%'", CommandType.Text, null);
        }

        
        public DataSet LaySoLuongDoanVienThamGia()
        {
            return db.ExecuteQueryDataSet("select d.MaHD, d.ThoiGian, COUNT(*) " +
                " from DanhGia d" +
                " group by d.MaHD, d.ThoiGian", CommandType.Text, null);
        }

        public DataSet LayLop()
        {
            return db.ExecuteQueryDataSet("select * from Lop", CommandType.Text, null);
        }

        public DataSet ThongKeHoatDong()
        {
            return db.ExecuteQueryDataSet("ThongKe",
                CommandType.StoredProcedure, null);
        }

        public DataSet TimKiemHD(string malop, string hocky, string nam)
        {
            return db.ExecuteQueryDataSet("with a as (select d.MaHD, d.ThoiGian, COUNT(*) as SL from DanhGia d group by d.MaHD, d.ThoiGian), " +
                " b as (select t.MaHD, t.ThoiGian, t.KinhPhiHoTro+t.KinhPhiKhac as KinhPhi, t.MaQL," +
                " (case when (a.SL>=t.SoLuong) then 25 else 0 end) As DiemSoLuong," +
                " (case when t.ChiTieuTienDo='True' then 25 else 0 end) as DiemTienDo," +
                " (case when t.ChiTieuChatLuong='True' then 25 else 0 end) as DiemChatLuong," +
                " (case when t.ChiTieuHieuQuaKinhPhi='True' then 25 else 0 end) as DiemKinhPhi" +
                " from TTHoatDong t left join a on t.MaHD=a.MaHD and t.ThoiGian=a.ThoiGian)," +
                " c as (select b.MaHD, b.ThoiGian, b.DiemChatLuong+b.DiemKinhPhi+b.DiemSoLuong+b.DiemTienDo as Diem from b)," +
                " d as (select c.MaHD, c.ThoiGian," +
                " (case c.Diem when '0' then 'F' when '25' then 'D' when '50' then 'C' when '75' then 'B' when '100' then 'A' end) as XL from c)," +
                " e as (select b.MaHD, b.ThoiGian," +
                " (case MONTH(b.ThoiGian) when 8 then 1 when 9 then 1 when 10 then 1 when 11 then 1 when 12 then 1 when 1 then 1 else 2 end) as HocKi from b)" +
                " select distinct b.MaHD, b.ThoiGian, v.MaLop, b.KinhPhi, a.SL, c.Diem, d.XL" +
                " from a right join b on a.MaHD=b.MaHD and a.ThoiGian=b.ThoiGian ,c,d, DoanVien v, e" +
                " where b.MaHD=c.MaHD and b.MaHD=d.MaHD and b.ThoiGian=c.ThoiGian and b.ThoiGian=d.ThoiGian and b.MaQL=v.MaDV and b.MaHD=e.MaHD and b.ThoiGian=e.ThoiGian" +
                " and v.MaLop like '%"+malop+"%' and YEAR(b.ThoiGian) like '%"+nam+"%' and e.HocKi like '%"+hocky+"%'", CommandType.Text, null);
        }

        public DataSet LayTraCuuHD()
        {
            return db.ExecuteQueryDataSet("select t.MaHD,c.TenHD,MaQL,ThoiGian,ThoiGianKetThuc,DiaDiem,SoLuong,KinhPhiHoTro,KinhPhiKhac,MaDVTC from TTHoatDong t, CTHoatDong c where t.MaHD=c.MaHD", CommandType.Text, null);
        }

        public DataSet TraCuuTTHD(string malop, string thang, string nam, string tenhd)
        {
            return db.ExecuteQueryDataSet("select t.MaHD,c.TenHD,MaQL,ThoiGian,ThoiGianKetThuc,DiaDiem,SoLuong,KinhPhiHoTro,KinhPhiKhac,MaDVTC " +
                " from TTHoatDong t, CTHoatDong c, DoanVien v" +
                " where t.MaHD=c.MaHD and v.MaDV=t.MaQL and c.TenHD like '%" + tenhd + "%' and MONTH(t.ThoiGian) like '%" + thang + "%' and YEAR(t.ThoiGian) like'%" + nam + "%' and v.MaLop like '%" + malop + "%'", 
                CommandType.Text, null);
        }
    }
}
